#!/usr/bin/python3
# -*- coding: utf-8 -*-

from sqlalchemy import create_engine, MetaData
from sqlalchemy.sql import select
from datetime import datetime, date
from db import Question, Choice

DSN = "sqlite:///{db_file}?check_same_thread=False"

def create_tables(engine):
    meta = MetaData()
    meta.create_all(bind=engine, tables=[Question, Choice])

def sample_data(engine):
    conn = engine.connect()
    conn.execute(Question.insert(), [
        {'question_text': 'What\'s new?',
         'pub_date': date.today()}
    ])
    conn.execute(Choice.insert(), [
        {'choice_text': 'Not much', 'votes': 0, 'question_id': 1},
        {'choice_text': 'The sky', 'votes': 0, 'question_id': 1},
        {'choice_text': 'Just hacking again', 'votes': 0, 'question_id': 1},
    ])
    conn.close()

if __name__ == '__main__':
    db_url = DSN.format(db_file = 'demo.db')
    engine = create_engine(db_url, echo=True)
    create_tables(engine)
    sample_data(engine)
    conn = engine.connect()
    # insert
    results = conn.execute(Choice.insert(), {'choice_text': 'nothing', 'votes': 1, 'question_id': 1})
    print(str(results))
    # select
    results = conn.execute(Choice.select(), {'question_id': 1})
    for result in results:
        print(result)
    # update
    conn.execute(Choice.update().where(Choice.columns['id'] == 1), {'choice_text': 'nothing at All', 'votes': 1})
    results = conn.execute(Choice.select(), {'question_id': 1})
    for result in results:
        print(result)
    # delete
    conn.execute(Choice.delete(), {'votes': 1})
    results = conn.execute(Choice.select(), {'question_id': 1})
    for result in results:
        print(result)
    conn.close()
